package com.water.devtool;

import java.io.File;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;

import com.watermud.util.FileUtil;
import com.watermud.util.StringUtil;
import com.watermud.util.db.ConnectionUtil;
import com.watermud.util.db.DBUtil;

//
public class DataExportUtil {

	public static String getWhereSql(String key, String value) {
		return key + " = '" + value + "'";
	}

	public static String getWhereSql(String[] key, String[] value) {
		StringBuilder str = new StringBuilder();
		for (int i = 0; i < key.length; i++) {
			if (i > 0) {
				str.append(" and ");
			}
			str.append(key[i]).append(" = '").append(value[i]).append("'");
		}
		return str.toString();
	}

	@Deprecated
	public static String export(String tableName, String column, String value,
			boolean comment) {
		String descSql = TableUtil.columnSql;
		String delSql = "delete from " + tableName + " where " + column
				+ " =  '" + value + "';";
		StringBuilder result = new StringBuilder();
		if (comment) {
			result.append("/***************导出开始************/").append("\r\n");
		}
		result.append(delSql);
		try {
			ResultSet rs = DBUtil.executeQuery(descSql,
					new Object[] { tableName.toUpperCase() });
			Map dataMap = new HashMap();
			StringBuilder desc = new StringBuilder();
			int i = 0;
			while (rs.next()) {
				String name = rs.getString("COLUMN_NAME");
				String type = rs.getString("DATA_TYPE");
				dataMap.put(name, type);
				if (i > 0) {
					desc.append(",");
				}
				if (type.startsWith("DATE") || type.startsWith("DATETIME")
						|| type.startsWith("TIMESTAMP")) {
					desc.append("to_char(").append(name)
							.append(",'yyyymmdd hh24:mi:ss') ").append(name);
				} else {
					desc.append(name);
				}
				i++;
			}
			if (dataMap.size() == 0) {
				throw new RuntimeException("查不到该表：" + tableName);
			}
			String selSql = "select " + desc.toString() + "  from " + tableName
					+ " where " + column + " =  ? ";
			rs = DBUtil.executeQuery(selSql, new Object[] { value });
			int count = 0;
			while (rs.next()) {
				count++;
				result.append("\r\n").append(rsToSql(tableName, dataMap, rs));
			}
			if (count == 0) {
				return "";
			}
			if (comment) {
				result.append("/***************导出结束************/").append(
						"\r\n");
			}
			return result.toString();
		} catch (Exception e) {
			e.printStackTrace();
			if (e instanceof RuntimeException) {
				throw (RuntimeException) e;
			} else {
				throw new RuntimeException("导出数据出错" + e.getMessage(), e);
			}
		} finally {
			ConnectionUtil.removeConnection();
		}

	}

	public static String exportUseSql(String tableName, String sql,
			boolean comment) {
		String descSql = TableUtil.columnSql;
		String delSql = "delete from " + tableName;
		if (StringUtil.isNotNull(sql)) {
			delSql = delSql + " where  " + sql;
		}
		delSql = delSql + ";";
		StringBuilder result = new StringBuilder();
		if (comment) {
			result.append("/***************导出开始************/").append("\r\n");
		}
		result.append(delSql);
		try {
			ResultSet rs = DBUtil.executeQuery(descSql,
					new Object[] { tableName.toUpperCase() });
			Map dataMap = new HashMap();
			StringBuilder desc = new StringBuilder();
			int i = 0;
			while (rs.next()) {
				String name = rs.getString("COLUMN_NAME");
				String type = rs.getString("DATA_TYPE");
				dataMap.put(name, type);
				if (i > 0) {
					desc.append(",");
				}
				if (type.startsWith("DATE") || type.startsWith("DATETIME")
						|| type.startsWith("TIMESTAMP")) {
					desc.append("to_char(").append(name)
							.append(",'yyyymmdd hh24:mi:ss') ").append(name);
				} else {
					desc.append(name);
				}
				i++;
			}
			if (dataMap.size() == 0) {
				throw new RuntimeException("查不到该表：" + tableName);
			}
			String selSql = "select " + desc.toString() + "  from " + tableName;
			if (StringUtil.isNotNull(sql)) {
				selSql = selSql + " where  " + sql;
			}
			rs = DBUtil.executeQuery(selSql, new Object[] {});
			int count = 0;
			while (rs.next()) {
				count++;
				result.append("\r\n").append(rsToSql(tableName, dataMap, rs));
			}
			if (count == 0) {
				return "";
			}
			if (comment) {
				result.append("/***************导出结束************/").append(
						"\r\n");
			}
			return result.toString();
		} catch (Exception e) {
			e.printStackTrace();
			if (e instanceof RuntimeException) {
				throw (RuntimeException) e;
			} else {
				throw new RuntimeException("导出数据出错" + e.getMessage(), e);
			}
		} finally {
			ConnectionUtil.removeConnection();
		}

	}

	public static String exportUseSql(String tableName, String sql, String k,
			String seq, boolean comment) {
		String descSql = TableUtil.columnSql;
		String delSql = "delete from " + tableName + " where  " + sql + ";";
		StringBuilder result = new StringBuilder();
		if (comment) {
			result.append("/***************导出开始************/").append("\r\n");
		}
		result.append(delSql);
		try {
			ResultSet rs = DBUtil.executeQuery(descSql,
					new Object[] { tableName.toUpperCase() });
			Map dataMap = new HashMap();
			StringBuilder desc = new StringBuilder();
			int i = 0;
			while (rs.next()) {
				String name = rs.getString("COLUMN_NAME");
				String type = rs.getString("DATA_TYPE");
				dataMap.put(name, type);
				if (i > 0) {
					desc.append(",");
				}
				if (type.startsWith("DATE") || type.startsWith("DATETIME")
						|| type.startsWith("TIMESTAMP")) {
					desc.append("to_char(").append(name)
							.append(",'yyyymmdd hh24:mi:ss') ").append(name);
				} else {
					desc.append(name);
				}
				i++;
			}
			if (dataMap.size() == 0) {
				throw new RuntimeException("查不到该表：" + tableName);
			}
			String selSql = "select " + desc.toString() + "  from " + tableName
					+ "  where  " + sql;
			rs = DBUtil.executeQuery(selSql, new Object[] {});
			int count = 0;
			while (rs.next()) {
				count++;
				result.append("\r\n").append(
						rsToSqlUseSeq(tableName, dataMap, rs, k, seq));
			}
			if (count == 0) {
				return "";
			}
			result.append("/***************导出结束************/").append("\r\n");
			return result.toString();
		} catch (Exception e) {
			e.printStackTrace();
			if (e instanceof RuntimeException) {
				throw (RuntimeException) e;
			} else {
				throw new RuntimeException("导出数据出错" + e.getMessage(), e);
			}
		} finally {
			ConnectionUtil.removeConnection();
		}

	}

	private static String rsToSql(String tableName, Map dataMap, ResultSet rs)
			throws SQLException {
		Set keys = dataMap.keySet();
		StringBuilder columns = new StringBuilder();
		StringBuilder values = new StringBuilder();
		columns.append("insert into ").append(tableName).append("( ");
		values.append(" values( ");
		int i = 0;
		int columnLen = 1;
		int valueLen = 1;
		for (Iterator iterator = keys.iterator(); iterator.hasNext();) {
			String key = (String) iterator.next();
			String type = (String) dataMap.get(key);
			String result = rs.getString(key);
			if (result == null || result.equals("")) {
				continue;
			}
			result = result.replaceAll("'", "''");
			result = result.replaceAll("&", "' || chr(38) || '");

			if (columns.length() > columnLen * 75) {
				columns.append("\r\n");
				columnLen++;
			}
			if (values.length() > valueLen * 75) {
				values.append("\r\n");
				valueLen++;
			}
			if (i > 0) {
				columns.append(",");
				values.append(",");
			}
			columns.append(key);
			i++;

			if (type.equals("NUMBER")) {
				values.append(result);
			} else if (type.startsWith("DATE") || type.startsWith("DATETIME")
					|| type.startsWith("TIMESTAMP")) {
				values.append("to_date('").append(result)
						.append("','yyyymmdd hh24:mi:ss')");
			} else {
				values.append("'").append(result).append("'");
			}
		}
		columns.append(" ) \r\n");
		values.append(" ); \r\n");
		return columns.append(values).toString();
	}

	@Deprecated
	public static String exportUseSeq(String tableName, String column,
			String value, String key, String seq, boolean comment) {
		String descSql = TableUtil.columnSql;
		String delSql = "delete from " + tableName + " where " + column
				+ " =  '" + value + "';";
		StringBuilder result = new StringBuilder();
		if (comment) {
			result.append("/***************导出开始************/").append("\r\n");
		}
		result.append(delSql);
		try {
			ResultSet rs = DBUtil.executeQuery(descSql,
					new Object[] { tableName.toUpperCase() });
			Map dataMap = new HashMap();
			StringBuilder desc = new StringBuilder();
			int i = 0;
			while (rs.next()) {
				String name = rs.getString("COLUMN_NAME");
				String type = rs.getString("DATA_TYPE");
				dataMap.put(name, type);
				if (i > 0) {
					desc.append(",");
				}
				if (type.startsWith("DATE") || type.startsWith("DATETIME")
						|| type.startsWith("TIMESTAMP")) {
					desc.append("to_char(").append(name)
							.append(",'yyyymmdd hh24:mi:ss') ").append(name);
				} else {
					desc.append(name);
				}
				i++;
			}
			if (dataMap.size() == 0) {
				throw new RuntimeException("查不到该表：" + tableName);
			}
			String selSql = "select " + desc.toString() + "  from " + tableName
					+ " where " + column + " =  ? ";
			rs = DBUtil.executeQuery(selSql, new Object[] { value });
			int count = 0;
			while (rs.next()) {
				count++;
				result.append("\r\n").append(
						rsToSqlUseSeq(tableName, dataMap, rs, key, seq));
			}
			if (count == 0) {
				return "";
			}
			if (comment) {
				result.append("/***************导出结束************/").append(
						"\r\n");
			}
			return result.toString();
		} catch (Exception e) {
			e.printStackTrace();
			if (e instanceof RuntimeException) {
				throw (RuntimeException) e;
			} else {
				throw new RuntimeException("导出数据出错" + e.getMessage(), e);
			}
		} finally {
			ConnectionUtil.removeConnection();
		}

	}

	private static String rsToSqlUseSeq(String tableName, Map dataMap,
			ResultSet rs, String k, String seq) throws SQLException {
		Set keys = dataMap.keySet();
		StringBuilder columns = new StringBuilder();
		StringBuilder values = new StringBuilder();
		columns.append("insert into ").append(tableName).append("( ");
		values.append(" values( ");
		int i = 0;
		int columnLen = 1;
		int valueLen = 1;
		for (Iterator iterator = keys.iterator(); iterator.hasNext();) {
			boolean isKey = false;
			String key = (String) iterator.next();
			String type = (String) dataMap.get(key);
			String result = rs.getString(key);
			if (key.equalsIgnoreCase(k)) {
				result = seq;
				isKey = true;
			}
			if (result == null || result.equals("")) {
				continue;
			}
			result = result.replaceAll("'", "''");
			result = result.replaceAll("&", "' || chr(38) || '");
			if (columns.length() > columnLen * 75) {
				columns.append("\r\n");
				columnLen++;
			}
			if (values.length() > valueLen * 75) {
				values.append("\r\n");
				valueLen++;
			}
			if (i > 0) {
				columns.append(",");
				values.append(",");
			}
			columns.append(key);
			i++;
			if (isKey) {
				values.append(result);
			} else if (type.equals("NUMBER")) {
				values.append(result);
			} else if (type.startsWith("DATE") || type.startsWith("DATETIME")
					|| type.startsWith("TIMESTAMP")) {
				values.append("to_date('").append(result)
						.append("','yyyymmdd hh24:mi:ss')");
			} else {
				values.append("'").append(result).append("'");
			}
		}
		columns.append(" ) \r\n");
		values.append(" ); \r\n");
		return columns.append(values).toString();
	}

	public static String exportUseArray(String tableName, String sql,
			String[] key, String[] val, boolean comment, boolean delete) {
		String descSql = TableUtil.columnSql;

		StringBuilder result = new StringBuilder();
		if (comment) {
			result.append("/***************导出开始************/").append("\r\n");
		}
		if (delete) {
			String delSql = "delete from " + tableName + " where " + sql + ";";
			result.append(delSql);
		}
		try {
			ResultSet rs = DBUtil.executeQuery(descSql,
					new Object[] { tableName.toUpperCase() });
			Map dataMap = new HashMap();
			StringBuilder desc = new StringBuilder();
			int i = 0;
			while (rs.next()) {
				String name = rs.getString("COLUMN_NAME");
				String type = rs.getString("DATA_TYPE");
				dataMap.put(name, type);
				if (i > 0) {
					desc.append(",");
				}
				if (type.startsWith("DATE") || type.startsWith("DATETIME")
						|| type.startsWith("TIMESTAMP")) {
					desc.append("to_char(").append(name)
							.append(",'yyyymmdd hh24:mi:ss') ").append(name);
				} else {
					desc.append(name);
				}
				i++;
			}
			if (dataMap.size() == 0) {
				throw new RuntimeException("查不到该表：" + tableName);
			}
			String selSql = "select " + desc.toString() + "  from " + tableName
					+ " where " + sql + "  ";
			rs = DBUtil.executeQuery(selSql, new Object[] {});
			int count = 0;
			while (rs.next()) {
				count++;
				result.append("\r\n").append(
						rsToSqlUseArray(tableName, dataMap, rs, key, val));
			}
			if (count == 0) {
				return "";
			}
			if (comment) {
				result.append("/***************导出结束************/").append(
						"\r\n");
			}
			return result.toString();
		} catch (Exception e) {
			e.printStackTrace();
			if (e instanceof RuntimeException) {
				throw (RuntimeException) e;
			} else {
				throw new RuntimeException("导出数据出错" + e.getMessage(), e);
			}
		} finally {
			ConnectionUtil.removeConnection();
		}

	}

	public static String exportUseArrayWithPre(String tableName, String sql,
			String[] key, String[] val, boolean comment, boolean delete,
			Map preAppend) {
		String descSql = TableUtil.columnSql;

		StringBuilder result = new StringBuilder();
		if (comment) {
			result.append("/***************导出开始************/").append("\r\n");
		}
		if (delete) {
			String delSql = "delete from " + tableName + " where " + sql + ";";
			result.append(delSql);
		}
		try {
			ResultSet rs = DBUtil.executeQuery(descSql,
					new Object[] { tableName.toUpperCase() });
			Map dataMap = new HashMap();
			StringBuilder desc = new StringBuilder();
			int i = 0;
			while (rs.next()) {
				String name = rs.getString("COLUMN_NAME");
				String type = rs.getString("DATA_TYPE");
				dataMap.put(name, type);
				if (i > 0) {
					desc.append(",");
				}
				if (type.startsWith("DATE") || type.startsWith("DATETIME")
						|| type.startsWith("TIMESTAMP")) {
					desc.append("to_char(").append(name)
							.append(",'yyyymmdd hh24:mi:ss') ").append(name);
				} else {
					desc.append(name);
				}
				i++;
			}
			if (dataMap.size() == 0) {
				throw new RuntimeException("查不到该表：" + tableName);
			}
			String selSql = "select " + desc.toString() + "  from " + tableName
					+ " where " + sql + "  ";
			rs = DBUtil.executeQuery(selSql, new Object[] {});
			int count = 0;
			while (rs.next()) {
				count++;
				result.append("\r\n").append(
						rsToSqlUseArray(tableName, dataMap, rs, key, val));
			}
			if (count == 0) {
				return "";
			}
			if (comment) {
				result.append("/***************导出结束************/").append(
						"\r\n");
			}
			return result.toString();
		} catch (Exception e) {
			e.printStackTrace();
			if (e instanceof RuntimeException) {
				throw (RuntimeException) e;
			} else {
				throw new RuntimeException("导出数据出错" + e.getMessage(), e);
			}
		} finally {
			ConnectionUtil.removeConnection();
		}

	}

	private static String rsToSqlUseArray(String tableName, Map dataMap,
			ResultSet rs, String[] k, String[] val) throws SQLException {
		Set keys = dataMap.keySet();
		StringBuilder columns = new StringBuilder();
		StringBuilder values = new StringBuilder();
		columns.append("insert into ").append(tableName).append("( ");
		values.append(" values( ");
		int i = 0;
		int columnLen = 1;
		int valueLen = 1;
		for (Iterator iterator = keys.iterator(); iterator.hasNext();) {
			boolean isKey = false;
			String key = (String) iterator.next();
			String type = (String) dataMap.get(key);
			String result = rs.getString(key);
			if (k != null) {
				for (int j = 0; j < k.length; j++) {
					if (key.equalsIgnoreCase(k[j])) {
						result = val[j];
						isKey = true;
						break;
					}
				}
			}

			if (result == null || result.equals("")) {
				continue;
			}
			result = result.replaceAll("'", "''");
			result = result.replaceAll("&", "' || chr(38) || '");
			if (columns.length() > columnLen * 75) {
				columns.append("\r\n");
				columnLen++;
			}
			if (values.length() > valueLen * 75) {
				values.append("\r\n");
				valueLen++;
			}
			if (i > 0) {
				columns.append(",");
				values.append(",");
			}
			columns.append(key);
			i++;
			if (isKey) {
				values.append(result);
			} else if (type.equals("NUMBER")) {
				values.append(result);
			} else if (type.startsWith("DATE") || type.startsWith("DATETIME")
					|| type.startsWith("TIMESTAMP")) {
				values.append("to_date('").append(result)
						.append("','yyyymmdd hh24:mi:ss')");
			} else {
				values.append("'").append(result).append("'");
			}
		}
		columns.append(" ) \r\n");
		values.append(" ); \r\n");
		return columns.append(values).toString();
	}

	private static String rsToSqlUseArray(String tableName, Map dataMap,
			ResultSet rs, String[] k, String[] val, Map preAppend)
			throws SQLException {
		Set keys = dataMap.keySet();
		StringBuilder columns = new StringBuilder();
		StringBuilder values = new StringBuilder();
		columns.append("insert into ").append(tableName).append("( ");
		values.append(" values( ");
		int i = 0;
		int columnLen = 1;
		int valueLen = 1;
		for (Iterator iterator = keys.iterator(); iterator.hasNext();) {
			boolean isKey = false;
			String key = (String) iterator.next();
			String type = (String) dataMap.get(key);
			String result = rs.getString(key);
			if (k != null) {
				for (int j = 0; j < k.length; j++) {
					if (key.equalsIgnoreCase(k[j])) {
						result = val[j];
						isKey = true;
						break;
					}
				}
			}

			if (result == null || result.equals("")) {
				continue;
			}
			result = result.replaceAll("'", "''");
			result = result.replaceAll("&", "' || chr(38) || '");
			if (columns.length() > columnLen * 75) {
				columns.append("\r\n");
				columnLen++;
			}
			if (values.length() > valueLen * 75) {
				values.append("\r\n");
				valueLen++;
			}
			if (i > 0) {
				columns.append(",");
				values.append(",");
			}
			columns.append(key);
			i++;
			String pre = (String) preAppend.get(key);
			if (pre != null) {
				result = pre + result;
			}
			if (isKey) {
				values.append(result);
			} else if (type.equals("NUMBER")) {
				values.append(result);
			} else if (type.startsWith("DATE") || type.startsWith("DATETIME")
					|| type.startsWith("TIMESTAMP")) {
				values.append("to_date('").append(result)
						.append("','yyyymmdd hh24:mi:ss')");
			} else {
				values.append("'").append(result).append("'");
			}
		}
		columns.append(" ) \r\n");
		values.append(" ); \r\n");
		return columns.append(values).toString();
	}

	public static void main(String[] args) {
		try {
			File f = new File("d:/a.txt");
			if (f.exists()) {
				f.delete();
			}
			FileUtil.append(
					f,
					export("T_JFW_DATAMODEL", "modelname", "CMD_PROD_VRF_TASK",
							true));
			FileUtil.append(
					f,
					export("t_jfw_fieldinfo", "modelname", "CMD_PROD_VRF_TASK",
							true));
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}
}
